db
Connect to remote DB via ssh tunneling
The DB you want to connect to is on a remote server and cannot be accessed directly from the outside, but you do have ssh access to the remote server.
Solution
First, open a terminal window and run the following command (keep it open while you want to maintain the connection to the DB):
ssh -L localhost:<local_port>:<remote_db_host>:<remote_db_port> <remote_ssh_user>@<remote_ssh_host> [-i <permission_file>.pem]
Then, you connect to the DB at localhost:<local_port>
and the remote DB credentials.
Prevent Postgres from automatically lower casing constraint names
This doesn’t work:
ALTER TABLE company DROP CONSTRAINT UQ_924dc2ee53aa15f1b16b4af12be;
Postgres says that this constraint doesn’t exist.
Solution
The problem is that Postgres will lowercase the constraint name. Use double quotes around the constraint name, like this:
ALTER TABLE company DROP CONSTRAINT "UQ_924dc2ee53aa15f1b16b4af12be";
MySQL – How to drop all tables starting with a prefix?
Solution
SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'prefix%' AND TABLE_SCHEMA = 'my_database' INTO outfile '/tmp/drop_my_tables.sql'; SOURCE /tmp/drop_my_tables.sql;
MySQL Dump with condition
Solution
mysqldump -u root -p pass my_database my_table --where="id IN (100, 101)" > my_table_dump.sql
MySQL – Search for tables by column name
Solution
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%name%' AND TABLE_SCHEMA = 'my_database';
How to see how much disk space a MySQL table is taking up?
Solution
SELECT table_schema as `Database`, table_name AS `Table`, ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;
Return empty array from aggregate in Prostgres
When you want to avoid NULL for the result of JSONB_AGG and instead you want to get an empty array.
Solution
Use COALESCE
and '[]'::JSONB
.
SELECT COALESCE( JSONB_AGG( JSONB_BUILD_OBJECT( 'id', role.id, 'name', role.name ) ) FILTER (WHERE role.id IS NOT NULL), '[]'::JSONB ) AS "adminRoles", FROM ...